﻿USE CodeStash
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OwnedTeams]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[OwnedTeams](
	[TeamId] [int] IDENTITY(1,1) NOT NULL,
	[TeamOwnerAspMembershipUserId] [uniqueidentifier] NOT NULL,
	[TeamDescription] [nvarchar](225) NOT NULL,
 CONSTRAINT [PK_OwnedTeams] PRIMARY KEY CLUSTERED 
(
	[TeamId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CreatedTeams]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CreatedTeams](
	[CreatedTeamId] [int] IDENTITY(1,1) NOT NULL,
	[TeamId] [int] NOT NULL,
	[AspMembershipUserId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_CreatedTeams_1] PRIMARY KEY CLUSTERED 
(
	[CreatedTeamId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Language]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Language](
	[LanguageId] [int] IDENTITY(1,1) NOT NULL,
	[Language] [nvarchar](50) NOT NULL,
	[LanguageCode] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_HighlightBrush] PRIMARY KEY CLUSTERED 
(
	[LanguageId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Groupings]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Groupings](
	[GroupId] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nchar](50) NOT NULL,
 CONSTRAINT [PK_Groupings] PRIMARY KEY CLUSTERED 
(
	[GroupId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CodeCategory]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CodeCategory](
	[CodeCategoryId] [int] IDENTITY(1,1) NOT NULL,
	[CodeCategoryName] [nchar](255) NOT NULL,
 CONSTRAINT [PK_CodeCategory] PRIMARY KEY CLUSTERED 
(
	[CodeCategoryId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Visibility]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Visibility](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[VisibilityDescription] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Visibility] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CodeSnippet]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CodeSnippet](
	[CodeSnippetId] [int] IDENTITY(1,1) NOT NULL,
	[ActualCode] [nvarchar](max) NOT NULL,
	[CodeCategoryId] [int] NOT NULL,
	[Description] [nvarchar](200) NOT NULL,
	[Title] [nvarchar](50) NOT NULL,
	[GroupId] [int] NULL,
	[LanguageId] [int] NOT NULL,
	[AspNetMembershipUserId] [uniqueidentifier] NOT NULL,
	[Visibility] [int] NOT NULL,
 CONSTRAINT [PK_CodeSnippet] PRIMARY KEY CLUSTERED 
(
	[CodeSnippetId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CodeTag]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CodeTag](
	[TagId] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nvarchar](100) NOT NULL,
	[MatchingSnippetId] [int] NOT NULL,
 CONSTRAINT [PK_Tag] PRIMARY KEY CLUSTERED 
(
	[TagId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CodeSnippet_CodeCategory]') AND parent_object_id = OBJECT_ID(N'[dbo].[CodeSnippet]'))
ALTER TABLE [dbo].[CodeSnippet]  WITH CHECK ADD  CONSTRAINT [FK_CodeSnippet_CodeCategory] FOREIGN KEY([CodeCategoryId])
REFERENCES [dbo].[CodeCategory] ([CodeCategoryId])
GO
ALTER TABLE [dbo].[CodeSnippet] CHECK CONSTRAINT [FK_CodeSnippet_CodeCategory]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CodeSnippet_CodeSnippet]') AND parent_object_id = OBJECT_ID(N'[dbo].[CodeSnippet]'))
ALTER TABLE [dbo].[CodeSnippet]  WITH CHECK ADD  CONSTRAINT [FK_CodeSnippet_CodeSnippet] FOREIGN KEY([Visibility])
REFERENCES [dbo].[Visibility] ([Id])
GO
ALTER TABLE [dbo].[CodeSnippet] CHECK CONSTRAINT [FK_CodeSnippet_CodeSnippet]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CodeSnippet_Groupings]') AND parent_object_id = OBJECT_ID(N'[dbo].[CodeSnippet]'))
ALTER TABLE [dbo].[CodeSnippet]  WITH CHECK ADD  CONSTRAINT [FK_CodeSnippet_Groupings] FOREIGN KEY([GroupId])
REFERENCES [dbo].[Groupings] ([GroupId])
GO
ALTER TABLE [dbo].[CodeSnippet] CHECK CONSTRAINT [FK_CodeSnippet_Groupings]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CodeSnippet_Language]') AND parent_object_id = OBJECT_ID(N'[dbo].[CodeSnippet]'))
ALTER TABLE [dbo].[CodeSnippet]  WITH CHECK ADD  CONSTRAINT [FK_CodeSnippet_Language] FOREIGN KEY([LanguageId])
REFERENCES [dbo].[Language] ([LanguageId])
GO
ALTER TABLE [dbo].[CodeSnippet] CHECK CONSTRAINT [FK_CodeSnippet_Language]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CodeTag_CodeSnippet]') AND parent_object_id = OBJECT_ID(N'[dbo].[CodeTag]'))
ALTER TABLE [dbo].[CodeTag]  WITH CHECK ADD  CONSTRAINT [FK_CodeTag_CodeSnippet] FOREIGN KEY([MatchingSnippetId])
REFERENCES [dbo].[CodeSnippet] ([CodeSnippetId])
GO
ALTER TABLE [dbo].[CodeTag] CHECK CONSTRAINT [FK_CodeTag_CodeSnippet]


/*************************************
   Add Static Data
*************************************/

-- Visibility
INSERT INTO [CodeStash].[dbo].[Visibility] ([VisibilityDescription])
     VALUES ('JustMe')
INSERT INTO [CodeStash].[dbo].[Visibility] ([VisibilityDescription])
     VALUES ('MyTeam')
INSERT INTO [CodeStash].[dbo].[Visibility] ([VisibilityDescription])
     VALUES ('Public')


-- Create Some Default Categories 
INSERT INTO [CodeStash].[dbo].[CodeCategory] ([CodeCategoryName]) VALUES ('C#')
INSERT INTO [CodeStash].[dbo].[CodeCategory] ([CodeCategoryName]) VALUES ('WF')
INSERT INTO [CodeStash].[dbo].[CodeCategory] ([CodeCategoryName]) VALUES ('WCF')
INSERT INTO [CodeStash].[dbo].[CodeCategory] ([CodeCategoryName]) VALUES ('WPF')
INSERT INTO [CodeStash].[dbo].[CodeCategory] ([CodeCategoryName]) VALUES ('Silverlight')
INSERT INTO [CodeStash].[dbo].[CodeCategory] ([CodeCategoryName]) VALUES ('MVVM')
INSERT INTO [CodeStash].[dbo].[CodeCategory] ([CodeCategoryName]) VALUES ('MVP')
INSERT INTO [CodeStash].[dbo].[CodeCategory] ([CodeCategoryName]) VALUES ('JQuery')
INSERT INTO [CodeStash].[dbo].[CodeCategory] ([CodeCategoryName]) VALUES ('ASP .NET')
INSERT INTO [CodeStash].[dbo].[CodeCategory] ([CodeCategoryName]) VALUES ('ASP .NET MVC')
INSERT INTO [CodeStash].[dbo].[CodeCategory] ([CodeCategoryName]) VALUES ('LINQ')
INSERT INTO [CodeStash].[dbo].[CodeCategory] ([CodeCategoryName]) VALUES ('RX')
INSERT INTO [CodeStash].[dbo].[CodeCategory] ([CodeCategoryName]) VALUES ('Python')
INSERT INTO [CodeStash].[dbo].[CodeCategory] ([CodeCategoryName]) VALUES ('IOC')


-- Language
INSERT INTO [CodeStash].[dbo].[Language] ([Language],[LanguageCode])
     VALUES ('C' ,'c')
INSERT INTO [CodeStash].[dbo].[Language] ([Language],[LanguageCode])
     VALUES ('C++' ,'cpp')
INSERT INTO [CodeStash].[dbo].[Language] ([Language],[LanguageCode])
     VALUES ('C#' ,'csharp')
INSERT INTO [CodeStash].[dbo].[Language] ([Language],[LanguageCode])
     VALUES ('CSS' ,'css')
INSERT INTO [CodeStash].[dbo].[Language] ([Language],[LanguageCode])
     VALUES ('Flex' ,'flex')
INSERT INTO [CodeStash].[dbo].[Language] ([Language],[LanguageCode])
     VALUES ('HTML' ,'html')
INSERT INTO [CodeStash].[dbo].[Language] ([Language],[LanguageCode])
     VALUES ('Java' ,'java')
INSERT INTO [CodeStash].[dbo].[Language] ([Language],[LanguageCode])
     VALUES ('JavaScript' ,'javascript')
INSERT INTO [CodeStash].[dbo].[Language] ([Language],[LanguageCode])
     VALUES ('JavaScript with DOM' ,'javascript_dom')
INSERT INTO [CodeStash].[dbo].[Language] ([Language],[LanguageCode])
     VALUES ('Perl' ,'perl')
INSERT INTO [CodeStash].[dbo].[Language] ([Language],[LanguageCode])
     VALUES ('PHP' ,'php')
INSERT INTO [CodeStash].[dbo].[Language] ([Language],[LanguageCode])
     VALUES ('Python' ,'python')
INSERT INTO [CodeStash].[dbo].[Language] ([Language],[LanguageCode])
     VALUES ('Ruby' ,'ruby')
INSERT INTO [CodeStash].[dbo].[Language] ([Language],[LanguageCode])
     VALUES ('SQL' ,'sql')
INSERT INTO [CodeStash].[dbo].[Language] ([Language],[LanguageCode])
     VALUES ('XML' ,'xml')


